package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import db.DBConn;
import entities.CommentCount;
import entities.CommentEntity;
import entities.UserEntity;

public class CommentService {

	// 获得某个商品的分页评论
	public static List<CommentEntity> getComment(String goods_id, String start,
			String count) throws SQLException {

		List<CommentEntity> commentList = new ArrayList<CommentEntity>();

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_comment , shop_user "
				+ "where comment_userId = user_id and        "
				+ "comment_goodsId = " + goods_id
				+ " order by comment_time desc  limit " + start + "," + count;
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			CommentEntity comment = new CommentEntity();
			DateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
			comment.setId(rs.getInt("comment_id"));
			comment.setContent(rs.getString("comment_content"));
			comment.setGoodsId(rs.getInt("comment_goodsId"));
			comment.setRank(rs.getInt("comment_rank"));
			comment.setTime(format.format(rs.getDate("comment_time")));
			comment.setTitle(rs.getString("comment_title"));
			comment.setUserId(rs.getInt("comment_userId"));
			comment.setUserName(rs.getString("user_name"));
			comment.setUserHead(rs.getString("user_headImg"));
			commentList.add(comment);
		}
		rs.close();
		stmt.close();
		conn.close();
		return commentList;
	}

	// 获得某个用户的分页评论
	public static List<CommentEntity> getUserComment(String userName,
			String start, String count) throws SQLException {

		List<CommentEntity> commentList = new ArrayList<CommentEntity>();
		UserEntity user = UserService.getUser(userName);

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_comment , shop_user,shop_goods "
				+ " where comment_userId = user_id and comment_goodsId = goods_id and comment_userId = "
				+ user.getId() + " order by comment_time desc  limit " + start
				+ "," + count;
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			CommentEntity comment = new CommentEntity();
			DateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
			comment.setId(rs.getInt("comment_id"));
			comment.setContent(rs.getString("comment_content"));
			comment.setGoodsId(rs.getInt("comment_goodsId"));
			comment.setRank(rs.getInt("comment_rank"));
			comment.setTime(format.format(rs.getDate("comment_time")));
			comment.setTitle(rs.getString("comment_title"));
			comment.setUserId(rs.getInt("comment_userId"));
			comment.setGoodsImg(rs.getString("goods_image"));
			comment.setGoodsName(rs.getString("goods_name"));
			comment.setUserName(rs.getString("user_name"));
			commentList.add(comment);
		}
		rs.close();
		stmt.close();
		conn.close();
		return commentList;
	}

	// 评论统计
	public static CommentCount getCommentCount(String goods_id)
			throws SQLException {
		CommentCount comC = new CommentCount();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select comment_goodsId,comment_rank,count(*) as comment_count from shop_comment  where  comment_goodsId = "
				+ goods_id + " group by comment_rank";

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			switch (rs.getInt("comment_rank")) {
			case 1:
				comC.setStar1(rs.getInt("comment_count"));
				break;
			case 2:
				comC.setStar2(rs.getInt("comment_count"));
				break;
			case 3:
				comC.setStar3(rs.getInt("comment_count"));
				break;
			case 4:
				comC.setStar4(rs.getInt("comment_count"));
				break;
			case 5:
				comC.setStar5(rs.getInt("comment_count"));
				break;
			default:
				break;
			}
			comC.setAll_star();
		}
		rs.close();
		stmt.close();
		conn.close();
		return comC;
	}

	// 添加评论
	public static String addComment(String userName, String goodsId,
			String title, String content, String score) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "INSERT INTO `shop_comment` VALUES (null, '" + goodsId
				+ "', '" + user.getId() + "', '" + title + "', '" + content
				+ "', '" + score + "', '" + sdf.format(new Date()) + "', '0')";

		int rs = stmt.executeUpdate(sql);

		stmt.close();
		conn.close();
		return rs > 0 ? "success" : "failed";
	}

	// 获得某个用户的评论总数
	public static int getUserCommentCount(String userName) throws SQLException {

		UserEntity user = UserService.getUser(userName);

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select count(*) from shop_comment , shop_user "
				+ " where comment_userId = user_id and comment_userId = "
				+ user.getId();
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		int result = 0;
		while (rs.next()) {
			result = rs.getInt(1);
		}
		rs.close();
		stmt.close();
		conn.close();
		return result;
	}

	// 获得某个商家的评论总数
	public static int getSellerCommentCount(String sellerName)
			throws SQLException {

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select count(*) from shop_comment , shop_seller, shop_goods "
				+ " where comment_goodsId = goods_id and goods_sellerId = seller_id and seller_name like '"
				+ sellerName + "'";
		System.out.println(sql);
		int result = 0;
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			result = rs.getInt(1);
		}
		rs.close();
		stmt.close();
		conn.close();
		return result;
	}

	// 获得某个用户的分页评论
	public static List<CommentEntity> getSelelrComment(String sellerName,
			String start, String count) throws SQLException {
		List<CommentEntity> commentList = new ArrayList<CommentEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_comment , shop_seller,shop_goods where  comment_goodsId = goods_id and goods_sellerId = seller_id and seller_name like '"
				+ sellerName
				+ "' order by comment_time desc  limit "
				+ start
				+ "," + count;
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			CommentEntity comment = new CommentEntity();
			DateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
			comment.setId(rs.getInt("comment_id"));
			comment.setContent(rs.getString("comment_content"));
			comment.setGoodsId(rs.getInt("comment_goodsId"));
			comment.setRank(rs.getInt("comment_rank"));
			comment.setTime(format.format(rs.getDate("comment_time")));
			comment.setTitle(rs.getString("comment_title"));
			comment.setUserId(rs.getInt("comment_userId"));
			comment.setGoodsImg(rs.getString("goods_image"));
			comment.setGoodsName(rs.getString("goods_name"));
			commentList.add(comment);
		}
		rs.close();
		stmt.close();
		conn.close();
		return commentList;
	}

	public static void main(String args[]) throws SQLException {
		// String[] title = { "好评", "中评", "差评" };
		// String[] content = { "一个一个来啊，哈哈我就说嘛，这么好的东西怎么可能没人吃呢，你说是不是啊",
		// "不错的样子，就是有点贵哦,可能下次还会来的的，看店家表现啦！！！！",
		// "肉都冒得，命苦咧,太难吃了~~~,没有下次了，哼哼！！！！！" };
		//
		// double[] score = { 5.0, 4.0, 2.0 };
		//
		// for (int i = 0; i < 100; i++) {
		// int r = new Random().nextInt(31);
		// String user = "test" + (i % 50);
		// int tcs = r % 3;
		// System.out.println(addComment(user, r + "", title[tcs],
		// content[tcs], score[tcs] + ""));
		//
		// }

		System.out.println(getSellerCommentCount("豪嘉基"));
		System.out.println(getSelelrComment("鑫客家小吃", "0", "10"));

		// addComment("liwentao", "1", "测试", "新评论", "3");

		// System.out.println(getUserCommentCount("liwentao"));
	}

}
